﻿CREATE PROCEDURE [dbo].[Articles_UpdateStatus]
	@RangeReviewId int
AS


declare @totalSales int = (select COUNT(*) 
						  from ArticleSales s
						  inner join Articles ar on ar.Id = s.ArticleId
						  where ar.RangeReviewId = @RangeReviewId)

IF @totalSales > 0
BEGIN

	DECLARE @StatusTable TABLE (Id INT, IsNew BIT, IsDelisted BIT)  

	INSERT INTO @StatusTable (Id, IsNew, IsDelisted)
	Select
		a.Id 
		, CASE WHEN sales.Id IS NULL OR (sales.SalesPY = 0 AND sales.Sales = 0) THEN 1 ELSE 0 END AS IsNew
		, CASE WHEN sales.Id IS NOT NULL AND sales.SalesPY > 0 AND sales.Sales = 0 THEN 1 ELSE 0 END AS IsDelisted --Productos que tienen venta el prior year pero este no se consideran delistados y no entran en el pareto
		
	From Articles a
	Left Outer Join 
	(
		Select Id, ArticleId, SUM(SalesPY) SalesPY, SUM(Sales) Sales From (
			Select rr.Id, s.ArticleId, CASE WHEN s.Date < rr.FirstMonth THEN SUM(s.Sales) ELSE 0 END AS SalesPY
				, CASE WHEN s.Date >= rr.FirstMonth THEN SUM(s.Sales) ELSE 0 END AS Sales 
			From ArticleSales s
			Inner Join RangeReviews rr on rr.Id = @RangeReviewId 
				and s.Date BETWEEN DATEADD(Month, -12 * rr.HasPriorYear, rr.FirstMonth)
					and DATEADD(Month, rr.TotalMonths, rr.FirstMonth)
			group by
				rr.Id
				, s.ArticleId
				, rr.FirstMonth
				, s.Date
			) data 
		group by
			Id
			, ArticleId
		) sales on a.RangeReviewId = sales.Id and a.Id = sales.ArticleId
	Where
		a.RangeReviewId = @RangeReviewId
	Order By a.Id

	--Actualizo el Status
	Update Articles
	Set IsDelisted = s.IsDelisted
	From Articles a
	Inner Join @StatusTable s on a.Id = s.Id and a.IsDelisted <> s.IsDelisted and s.IsDelisted = 1 --Solo delisto los activos (para el caso de Art.Master separado)

	--Actualizo el IsNew
	Update Articles
	Set IsNew = s.IsNew
	From Articles a
	Inner Join @StatusTable s on a.Id = s.Id and a.IsNew <> s.IsNew

	--Actualizo el IsDeletedRR
	Update Articles
	Set IsDeletedRangeReview = CASE WHEN IsDelisted = 1 OR IsNew = 1 THEN 1 ELSE 0 END
	Where IsDeletedRangeReview <> (CASE WHEN IsDelisted = 1 OR IsNew = 1 THEN 1 ELSE 0 END)

END